"""
创建笔记相关数据表
"""
import sys
import os

# 添加项目根目录到Python路径
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from sqlalchemy import create_engine, text
from app.core.config import settings
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


def create_notes_tables():
    """创建笔记相关表"""
    engine = create_engine(settings.DATABASE_URL)
    
    with engine.connect() as conn:
        # 创建笔记主表
        logger.info("创建 notes 表...")
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS notes (
                id VARCHAR(36) PRIMARY KEY,
                title VARCHAR(255) NOT NULL,
                content TEXT NOT NULL,
                owner_id VARCHAR(36) NOT NULL,
                owner_name VARCHAR(100),
                knowledge_base_id VARCHAR(36),
                is_vectorized BOOLEAN DEFAULT FALSE,
                vector_store_id VARCHAR(100),
                is_public BOOLEAN DEFAULT FALSE,
                metadata TEXT,
                word_count INTEGER DEFAULT 0,
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP WITH TIME ZONE,
                FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY (knowledge_base_id) REFERENCES knowledge_bases(id) ON DELETE SET NULL
            )
        """))
        
        # 创建索引
        logger.info("创建 notes 表索引...")
        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_notes_title ON notes(title)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_notes_owner_id ON notes(owner_id)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_notes_kb_id ON notes(knowledge_base_id)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_notes_created_at ON notes(created_at)"))
        
        # 创建笔记版本表
        logger.info("创建 note_versions 表...")
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS note_versions (
                id VARCHAR(36) PRIMARY KEY,
                note_id VARCHAR(36) NOT NULL,
                title VARCHAR(255) NOT NULL,
                content TEXT NOT NULL,
                version_number INTEGER NOT NULL,
                created_by VARCHAR(36),
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
                FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
            )
        """))
        
        # 创建版本表索引
        logger.info("创建 note_versions 表索引...")
        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_note_versions_note_id ON note_versions(note_id)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_note_versions_created_at ON note_versions(created_at)"))
        
        # 创建笔记-文档关联表
        logger.info("创建 note_documents 表...")
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS note_documents (
                id VARCHAR(36) PRIMARY KEY,
                note_id VARCHAR(36) NOT NULL,
                document_id VARCHAR(36) NOT NULL,
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
                FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
                UNIQUE (note_id, document_id)
            )
        """))
        
        # 创建关联表索引
        logger.info("创建 note_documents 表索引...")
        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_note_documents_note_id ON note_documents(note_id)"))
        conn.execute(text("CREATE INDEX IF NOT EXISTS idx_note_documents_document_id ON note_documents(document_id)"))
        
        conn.commit()
        logger.info("✅ 笔记相关表创建成功！")


def check_tables():
    """检查表是否创建成功"""
    engine = create_engine(settings.DATABASE_URL)
    
    with engine.connect() as conn:
        # 检查表是否存在
        result = conn.execute(text("""
            SELECT table_name 
            FROM information_schema.tables 
            WHERE table_schema = 'public' 
            AND table_name IN ('notes', 'note_versions', 'note_documents')
            ORDER BY table_name
        """))
        
        tables = [row[0] for row in result]
        logger.info(f"已创建的笔记相关表: {tables}")
        
        # 检查 notes 表结构
        result = conn.execute(text("""
            SELECT column_name, data_type 
            FROM information_schema.columns 
            WHERE table_name = 'notes'
            ORDER BY ordinal_position
        """))
        
        logger.info("\nnotes 表结构:")
        for row in result:
            logger.info(f"  {row[0]}: {row[1]}")


if __name__ == "__main__":
    try:
        logger.info("开始创建笔记相关数据表...")
        create_notes_tables()
        check_tables()
        logger.info("\n✅ 所有笔记表创建完成！")
    except Exception as e:
        logger.error(f"❌ 创建表失败: {str(e)}")
        import traceback
        traceback.print_exc()
        sys.exit(1)

